package com.xbniao.gcfs.common.util.ExportExcel;

import com.xbniao.gcfs.common.ApiStatusCode;
import com.xbniao.gcfs.common.BaseException;
import com.xbniao.gcfs.common.enums.GcfsReceiptForecastStatusEnum;
import com.xbniao.gcfs.common.util.DateSyncUtil;
import com.xbniao.gcfs.dto.GcfsBox;
import com.xbniao.gcfs.dto.GcfsBoxCommodity;
import com.xbniao.gcfs.dto.GcfsReceiptForecast;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.math.BigDecimal;
import java.util.*;

/**
 * @Author:wangbin.
 * @Description.
 * @Date:Created in 14:48 2018/3/18.
 * @Modified By:
 */
public class ExportExcelUtil extends ExportExcelBase{

    /**
     * 读取一个 Workbook
     * @param wb
     * @throws Exception
     */
    public void read(Workbook wb,Object data) throws Exception{
        CustomsDeclarationDocumentsVo documentsVo = (CustomsDeclarationDocumentsVo) data;
        // 处理第一个sheet
        processSheet1(wb.getSheetAt(0),documentsVo);
        // 处理第二个sheet
        processSheet2(wb.getSheetAt(1),documentsVo);
        // 处理第三个sheet
        processSheet3(wb.getSheetAt(2),documentsVo);
    }

    private static void processSheet1(Sheet sheet,CustomsDeclarationDocumentsVo data)
    {
        // 处理第1个sheet
        // 修改第9行，第7列的单元格的值合同号
        POIExportExcelUtil.updateCellValue(sheet,8,6,data.getContractNo());
        // 修改第11行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,10,0,data.getInvoiceNo());
        // 修改第11行，第7列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,10,6,data.getDate());
        // 修改第13行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,12,0,data.getDestinationPort() + "," + data.getDestinationCountry());
        // 修改第17行，第4,6,7,8列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,16,3,data.getCountQty());
        POIExportExcelUtil.updateCellValue(sheet,16,5,data.getCountNetWeight());
        POIExportExcelUtil.updateCellValue(sheet,16,6,data.getCountGrossWeight());
        POIExportExcelUtil.updateCellValue(sheet,16,7,data.getCountVolume());
        // 修改第18行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,17,0,"Remark : All above materials should consist of "+data.getCountQty()+" pkgs inside the container.");

        short row_height = POIExportExcelUtil.getRowHeight(sheet,16);
        short row_height_pre = POIExportExcelUtil.getRowHeight(sheet,17);

        List<CommodityVo> list = data.getCommodityVos();
        if(list.size() != 1)
        {
            for(int i = 0 ; i < list.size() ; i++)
            {
                // 要增加的行数（总货品数减1）
                if(i != (list.size() - 1))
                    POIExportExcelUtil.copyRows(16+i,16+i,16+i,sheet);
                POIExportExcelUtil.updateCellValue(sheet,15+i,0,i+1);
                POIExportExcelUtil.updateCellValue(sheet,15+i,1,list.get(i).getCommodityTitle());
                POIExportExcelUtil.updateCellValue(sheet,15+i,2,list.get(i).getMerchantSku());
                POIExportExcelUtil.updateCellValue(sheet,15+i,3,list.get(i).getQty());
                POIExportExcelUtil.updateCellValue(sheet,15+i,5,list.get(i).getNetWeight());
                POIExportExcelUtil.updateCellValue(sheet,15+i,6,list.get(i).getGrossWeight());
                POIExportExcelUtil.updateCellValue(sheet,15+i,7,list.get(i).getVolume());
            }
        }
        else
        {
            POIExportExcelUtil.updateCellValue(sheet,15,0,1);
            POIExportExcelUtil.updateCellValue(sheet,15,1,list.get(0).getCommodityTitle());
            POIExportExcelUtil.updateCellValue(sheet,15,2,list.get(0).getMerchantSku());
            POIExportExcelUtil.updateCellValue(sheet,15,3,list.get(0).getQty());
            POIExportExcelUtil.updateCellValue(sheet,15,5,list.get(0).getNetWeight());
            POIExportExcelUtil.updateCellValue(sheet,15,6,list.get(0).getGrossWeight());
            POIExportExcelUtil.updateCellValue(sheet,15,7,list.get(0).getVolume());
        }

        POIExportExcelUtil.setRowHeight(sheet,15+list.size(),row_height);
        POIExportExcelUtil.setRowHeight(sheet,16+list.size(),row_height_pre);
    }

    private static void processSheet2(Sheet sheet,CustomsDeclarationDocumentsVo data)
    {
        // 处理第2个sheet
        // 修改第10行，第7列的单元格的值合同号
        POIExportExcelUtil.updateCellValue(sheet,9,5,data.getContractNo());
        // 修改第12行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,11,0,data.getInvoiceNo());
        // 修改第12行，第7列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,11,5,data.getDate());
        // 修改第14行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,13,0,"目的港：" + data.getDestinationPort() + "," + data.getDestinationCountry());
        // 修改第15行，第1列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,14,0,"FOB " + data.getFob());
        // 修改第20行，第4,7列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,19,3,data.getCountQty());
        POIExportExcelUtil.updateCellValue(sheet,19,6,data.getCountTotalPrice());
        short row_height = POIExportExcelUtil.getRowHeight(sheet,19);
        List<CommodityVo> list = data.getCommodityVos();

        if(list.size() != 1)
        {
            for(int i = 0 ; i < list.size() ; i++)
            {
                // 要增加的行数（总货品数减1）
                if(i != (list.size() - 1))
                    POIExportExcelUtil.copyRows(19+i,19+i,19+i,sheet);
                POIExportExcelUtil.updateCellValue(sheet,18+i,0,i+1);
                POIExportExcelUtil.updateCellValue(sheet,18+i,1,list.get(i).getCommodityTitle());
                POIExportExcelUtil.updateCellValue(sheet,18+i,2,list.get(i).getMerchantSku());
                POIExportExcelUtil.updateCellValue(sheet,18+i,3,list.get(i).getQty());
                POIExportExcelUtil.updateCellValue(sheet,18+i,5,list.get(i).getDeclarePrice());
                POIExportExcelUtil.updateCellValue(sheet,18+i,6,list.get(i).getTotalPrice());
            }
        }
        else
        {
            POIExportExcelUtil.updateCellValue(sheet,18,0,1);
            POIExportExcelUtil.updateCellValue(sheet,18,1,list.get(0).getCommodityTitle());
            POIExportExcelUtil.updateCellValue(sheet,18,2,list.get(0).getMerchantSku());
            POIExportExcelUtil.updateCellValue(sheet,18,3,list.get(0).getQty());
            POIExportExcelUtil.updateCellValue(sheet,18,5,list.get(0).getDeclarePrice());
            POIExportExcelUtil.updateCellValue(sheet,18,6,list.get(0).getTotalPrice());
        }

        POIExportExcelUtil.setRowHeight(sheet,18+list.size(),row_height);

    }

    private static void processSheet3(Sheet sheet,CustomsDeclarationDocumentsVo data)
    {
        // 处理第3个sheet
        // 修改第2行，第5列的单元格的值合同号
        POIExportExcelUtil.updateCellValue(sheet,1,4,data.getContractNo());
        // 修改第15行，第3,5列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,14,2,data.getCountQty());
        POIExportExcelUtil.updateCellValue(sheet,14,4,data.getCountTotalPrice());
        // 修改第18行，第3列的单元格的值
        POIExportExcelUtil.updateCellValue(sheet,17,2,"From " + data.getFob() +" to " + data.getDestinationPort() + " by Sea");

        // 获取原始14行的高度
        short row_height = POIExportExcelUtil.getRowHeight(sheet,14);
        List<CommodityVo> list = data.getCommodityVos();
        if(list.size() != 1)
        {
            for(int i = 0 ; i < list.size() ; i++)
            {
                // 要增加的行数（总货品数减1）
                if(i != (list.size() - 1))
                    POIExportExcelUtil.copyRows(14+i,14+i,14+i,sheet);
                POIExportExcelUtil.updateCellValue(sheet,13+i,0,list.get(i).getCommodityTitle());
                POIExportExcelUtil.updateCellValue(sheet,13+i,1,list.get(i).getMerchantSku());
                POIExportExcelUtil.updateCellValue(sheet,13+i,2,list.get(i).getQty());
                POIExportExcelUtil.updateCellValue(sheet,13+i,3,list.get(i).getDeclarePrice());
                POIExportExcelUtil.updateCellValue(sheet,13+i,4,list.get(i).getTotalPrice());
            }
        }
        else
        {
            POIExportExcelUtil.updateCellValue(sheet,13,0,list.get(0).getCommodityTitle());
            POIExportExcelUtil.updateCellValue(sheet,13,1,list.get(0).getMerchantSku());
            POIExportExcelUtil.updateCellValue(sheet,13,2,list.get(0).getQty());
            POIExportExcelUtil.updateCellValue(sheet,13,3,list.get(0).getDeclarePrice());
            POIExportExcelUtil.updateCellValue(sheet,13,4,list.get(0).getTotalPrice());
        }

        // 此处要修改的行为总货品数-1+14
        // 设置原来14行的高度，因为原来14行已经被顶到了下面
        POIExportExcelUtil.setRowHeight(sheet,13+list.size(),row_height);
    }

    public static CustomsDeclarationDocumentsVo convart(GcfsReceiptForecast forecast,
                                                        List<GcfsBox> boxList,
                                                        List<GcfsBoxCommodity> boxCommodityList) throws Exception
    {
        CustomsDeclarationDocumentsVo documentsVo = new CustomsDeclarationDocumentsVo();
        // 判断只有审核通过的收货预报才能操作
        if(forecast.getStatus().shortValue() == GcfsReceiptForecastStatusEnum.HAD_REJECT.getCode() ||
                forecast.getStatus().shortValue() == GcfsReceiptForecastStatusEnum.NEW_CREATE.getCode() ||
                forecast.getStatus().shortValue() == GcfsReceiptForecastStatusEnum.WAITING_CHECK.getCode() ||
                forecast.getStatus().shortValue() == GcfsReceiptForecastStatusEnum.GET_INVENTORY_CODE.getCode() ||
                forecast.getReviewTime() == null)
            throw new BaseException(ApiStatusCode.GCFS_FORECAST_STATUS_IS_NOT_AUDIT.getCode(),
                    ApiStatusCode.GCFS_FORECAST_STATUS_IS_NOT_AUDIT.getTitle());

        String number = DateSyncUtil.formatDate_yyyymmdd(forecast.getReviewTime())+"/" + getNum();
        // 设置合同号
        documentsVo.setContractNo("BJ" + number);
        // 设置发票号
        documentsVo.setInvoiceNo("INVOICE NO：BJYMX-" + number);
        // 设置日期
        documentsVo.setDate("DATE:" + DateSyncUtil.formatDate_point(forecast.getReviewTime()));
        // 设置目的港
        documentsVo.setDestinationPort(forecast.getDestinationPort());
        // 设置目的国家
        documentsVo.setDestinationCountry(forecast.getCurrencyName());
        // 设置fob
        documentsVo.setFob(forecast.getDisposalSite() + ",CHINA");
        // 处理货品信息
        List<CommodityVo> list = convartCommodity(boxList,boxCommodityList);
        // 设置货品信息
        documentsVo.setCommodityVos(list);
        // 处理总数信息
        Map<String,Object> map = processCount(list);
        // 设置总数量
        documentsVo.setCountQty((Integer)map.get("countQty"));
        // 设置总净重
        documentsVo.setCountNetWeight((BigDecimal)map.get("countNetWeight"));
        // 设置总毛重
        documentsVo.setCountGrossWeight((BigDecimal)map.get("countGrossWeight"));
        // 设置总体积
        documentsVo.setCountVolume((BigDecimal)map.get("countVolume"));
        // 设置总价
        documentsVo.setCountTotalPrice((BigDecimal)map.get("countTotalPrice"));

        return documentsVo;
    }

    /**
     * 处理货品信息
     * @param boxCommodityList
     * @return
     */
    private static List<CommodityVo> convartCommodity(List<GcfsBox> boxList,List<GcfsBoxCommodity> boxCommodityList)
    {
        List<CommodityVo> list = new ArrayList<>();
        Map<String,CommodityVo> map = new HashMap<>();

        Map<String,GcfsBox> boxMap = new HashMap<>();
        for(GcfsBox box : boxList)
            boxMap.put(box.getBoxOrderCode(),box);

        for(GcfsBoxCommodity commodity : boxCommodityList)
        {
            GcfsBox box = boxMap.get(commodity.getBoxOrderCode());
            // 是重复商品
            if(map.containsKey(commodity.getMerchantSku()))
            {
                CommodityVo vo = map.get(commodity.getMerchantSku());
                // 累加总个数
                vo.setQty(vo.getQty() +
                        (commodity.getRealNum() == null ? (commodity.getFillBoxNum() == null ? 0 : commodity.getFillBoxNum()) : commodity.getRealNum()));
                // 累加总净重
                if(box == null || (box.getForecastWeigh() == null && box.getRealWeigh() == null))
                    vo.setNetWeight(new BigDecimal(vo.getNetWeight().doubleValue() +
                            (((commodity.getWeight() == null ? 0 : commodity.getWeight())-0.6) *
                                    (commodity.getRealNum() == null ? (commodity.getFillBoxNum() == null ? 0 : commodity.getFillBoxNum()) : commodity.getRealNum()))).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setNetWeight(new BigDecimal(vo.getNetWeight().doubleValue() +
                            (box.getForecastWeigh() == null ? box.getRealWeigh() : box.getForecastWeigh()).doubleValue() - 0.6).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 累加总毛重
                if(box == null || (box.getForecastWeigh() == null && box.getRealWeigh() == null))
                    vo.setGrossWeight(new BigDecimal(vo.getGrossWeight().doubleValue() +
                            ((commodity.getWeight() == null ? 0 : commodity.getWeight()) *
                                    (commodity.getRealNum() == null ? (commodity.getFillBoxNum() == null ? 0 : commodity.getFillBoxNum()) : commodity.getRealNum()))).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setGrossWeight((vo.getGrossWeight().add((box.getForecastWeigh() == null ? box.getRealWeigh() : box.getForecastWeigh()))).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 累加总体积
                if(box == null || (box.getForecastVolumet() == null && box.getRealVolumet() == null))
                    vo.setVolume(new BigDecimal(vo.getVolume().doubleValue() +
                            ((commodity.getHeight() == null ? 0 : commodity.getHeight()) *
                                    (commodity.getWidth() == null ? 0 : commodity.getWidth()) *
                                    (commodity.getLength() == null ? 0 : commodity.getLength()) / 1000000)).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setVolume(new BigDecimal(vo.getVolume().doubleValue() +
                            (box.getForecastVolumet() == null ? box.getRealVolumet() : box.getForecastVolumet()).doubleValue()).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 累加总价值
                vo.setTotalPrice(new BigDecimal(vo.getTotalPrice().doubleValue() +
                        (commodity.getDeclarePrice() *
                                (commodity.getRealNum() == null ? (commodity.getFillBoxNum() == null ? 0 : commodity.getFillBoxNum()) : commodity.getRealNum()))).setScale(2,BigDecimal.ROUND_HALF_UP));
            }
            // 不是重复商品
            else
            {
                CommodityVo vo = new CommodityVo();
                // 设置货品名称
                vo.setCommodityTitle(commodity.getCommodityTitle());
                // 设置货品sku，规格
                vo.setMerchantSku(commodity.getMerchantSku());
                // 设置单价（申报价值）
                vo.setDeclarePrice(new BigDecimal(commodity.getDeclarePrice()).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 设置总数
                vo.setQty(commodity.getRealNum() == null ? (commodity.getFillBoxNum() == null ? 0 : commodity.getFillBoxNum()) : commodity.getRealNum());
                // 设置总净重
                if(box == null || (box.getForecastWeigh() == null && box.getRealWeigh() == null))
                    vo.setNetWeight(new BigDecimal(((commodity.getWeight() == null ? 0 : commodity.getWeight())-0.6) * vo.getQty()).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setNetWeight(new BigDecimal((box.getForecastWeigh() == null ? box.getRealWeigh() : box.getForecastWeigh()).doubleValue() - 0.6).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 设置总毛重
                if(box == null || (box.getForecastWeigh() == null && box.getRealWeigh() == null))
                    vo.setGrossWeight(new BigDecimal((commodity.getWeight() == null ? 0 : commodity.getWeight()) * vo.getQty()).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setGrossWeight((box.getForecastWeigh() == null ? box.getRealWeigh() : box.getForecastWeigh()).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 设置体积
                if(box == null || (box.getForecastVolumet() == null && box.getRealVolumet() == null))
                    vo.setVolume(new BigDecimal((commodity.getHeight() == null ? 0 : commodity.getHeight()) *
                            (commodity.getWidth() == null ? 0 : commodity.getWidth()) *
                            (commodity.getLength() == null ? 0 : commodity.getLength()) / 1000000).setScale(2,BigDecimal.ROUND_HALF_UP));
                else
                    vo.setVolume(new BigDecimal(box.getForecastVolumet() == null ? box.getRealVolumet() : box.getForecastVolumet()).setScale(2,BigDecimal.ROUND_HALF_UP));
                // 设置总价值
                vo.setTotalPrice(new BigDecimal(commodity.getDeclarePrice() * vo.getQty()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put(commodity.getMerchantSku(),vo);
            }
        }
        for(Map.Entry<String,CommodityVo> entry : map.entrySet())
            list.add(entry.getValue());
        return list;
    }

    /**
     * 计算总数量，净重，毛重，体积，总价
     * @param list
     * @return
     */
    private static Map<String,Object> processCount(List<CommodityVo> list)
    {
        Map<String,Object> map = new HashMap<>();
        // 总数量
        Integer countQty = 0;
        // 总净重
        BigDecimal countNetWeight = new BigDecimal(0);
        // 总毛重
        BigDecimal countGrossWeight = new BigDecimal(0);
        // 总体积
        BigDecimal countVolume = new BigDecimal(0);
        // 总价值
        BigDecimal countTotalPrice = new BigDecimal(0);

        for(CommodityVo vo : list)
        {
            countQty += vo.getQty();
            countNetWeight = countNetWeight.add(vo.getNetWeight());
            countGrossWeight = countGrossWeight.add(vo.getGrossWeight());
            countVolume = countVolume.add(vo.getVolume());
            countTotalPrice = countTotalPrice.add(vo.getTotalPrice());
        }

        map.put("countQty",countQty);
        map.put("countNetWeight",countNetWeight.setScale(2,BigDecimal.ROUND_HALF_UP));
        map.put("countGrossWeight",countGrossWeight.setScale(2,BigDecimal.ROUND_HALF_UP));
        map.put("countVolume",countVolume.setScale(2,BigDecimal.ROUND_HALF_UP));
        map.put("countTotalPrice",countTotalPrice.setScale(2,BigDecimal.ROUND_HALF_UP));
        return map;
    }

    /**
     * 获取一个999以内的随机数
     * @return
     */
    private static int getNum()
    {
        Random random = new Random();
        return random.nextInt(999);
    }
}
